/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import bean.Contenedor;
import bean.Documento;
import java.sql.*;
import java.util.ArrayList;
import javax.swing.JOptionPane;


public class SQLServerDAODocumento implements DAODocumento{

    @Override
    public ArrayList<Documento> queryAll() {
        
        ArrayList <Documento> documentList = new ArrayList<Documento>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Documento d;
        
        try {            
            Driver myDriver = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
            String connectionUrl =  "jdbc:sqlserver://inti.lab.inf.pucp.edu.pe:1433;"+
                                    "databaseName=inf282;user=inf282;password=inf282db;";
            conn = DriverManager.getConnection(connectionUrl);
            pstmt = conn.prepareStatement("SELECT * FROM G04_DOCUMENTO");
            rs =  pstmt.executeQuery();
            
            while (rs.next()){
                int idDoc = rs.getInt("IDDOCUMENTO");
                int idCli = rs.getInt("IDCLIENTE");
                String tipo = rs.getString("TIPO_DOCUMENTO");
                String rCom = rs.getString("REGIMEN_COMERCIAL");
                String pOrg = rs.getString("PUERTO_ORIGEN");
                String pEmb = rs.getString("PUERTO_EMBARQUE");
                String nave = rs.getString("NAVE");
                
                d= new Documento(idDoc,idCli,tipo,rCom,pOrg,pEmb,nave);
                documentList.add(d);
            }            
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
             try {if (rs != null) rs.close(); } 
             catch(Exception e){e.printStackTrace();}  
        } 
            return documentList;    
    }

    @Override
    public Documento queryById(String id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int insert(Documento d) {
        
        int result =0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl =  "jdbc:sqlserver://inti.lab.inf.pucp.edu.pe:1433;"+
                                    "databaseName=inf282;user=inf282;password=inf282db;";
            conn = DriverManager.getConnection(connectionUrl);
            String SQLString =  "INSERT INTO G04_DOCUMENTO(IDDOCUMENTO,IDCLIENTE,TIPO_DOCUMENTO,REGIMEN_COMERCIAL,PUERTO_ORIGEN,PUERTO_EMBARQUE,NAVE)"
                                + "VALUES(?,?,?,?,?,?,?)";
            
                pstmt = conn.prepareStatement(SQLString);
                
                pstmt.setString(1, String.valueOf(d.getId()));
                pstmt.setString(2, String.valueOf(d.getIdCliente()));
                pstmt.setString(3, d.getTipoDoc());
                pstmt.setString(4, d.getRegc());
                pstmt.setString(5, d.getPuertoOrigen());
                pstmt.setString(6, d.getPuertoEmbarque());
                pstmt.setString(7, d.getNave());
                
            result =  pstmt.executeUpdate();
            
            if (result == 0){
                throw new Exception();
            }            
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
             //5. Se cierra la conexión
             try {if (rs != null) rs.close(); } 
             catch(Exception e){e.printStackTrace();}  
        } 
        if(result==1){
        JOptionPane.showMessageDialog(null,"      Operación Exitosa!","Cliente",JOptionPane.INFORMATION_MESSAGE);}
        return result;
    }
   

    @Override
    public int update(Documento d) {
        
        int result =0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
                
        String cadId,cadIdCli,cadTipo,cadReg,cadPo,cadPe,cadNave;
        cadId=Integer.toString(d.getId());
        cadIdCli=Integer.toString(d.getIdCliente());
        cadTipo=d.getTipoDoc();
        cadReg=d.getRegc();
        cadPo=d.getPuertoOrigen();
        cadPe=d.getPuertoEmbarque();
        cadNave=d.getNave();
        
        try {
            
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl =  "jdbc:sqlserver://inti.lab.inf.pucp.edu.pe:1433;"+
                                    "databaseName=inf282;user=inf282;password=inf282db;";
            conn = DriverManager.getConnection(connectionUrl);
            String SQLString = null;
            SQLString = "UPDATE G04_DOCUMENTO SET "+
                        "IDDOCUMENTO = "+cadId+
                        ",IDCLIENTE = "+cadIdCli+
                        ",TIPO_DOCUMENTO="+cadTipo+
                        ",REGIMEN_COMERCIAL="+cadReg+
                        ",PUERTO_ORIGEN="+cadPo+
                        ",PUERTO_EMBARQUE="+cadPe+
                        ",NAVE="+cadNave+ 
                        " WHERE IDDOCUMENTO="+cadId+"";
            
            pstmt = conn.prepareStatement(SQLString);
                
            result =  pstmt.executeUpdate();
            
            if (result == 0){
                throw new Exception();
            }            
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
             //5. Se cierra la conexión
             try {if (rs != null) rs.close(); } 
             catch(Exception e){e.printStackTrace();}  
        } 
        if(result==1){
        JOptionPane.showMessageDialog(null,"      Operación Exitosa!","Cliente",JOptionPane.INFORMATION_MESSAGE);}
        return result;
    }

    @Override
    public int delete(String id) {
          
        int result =0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl =  "jdbc:sqlserver://inti.lab.inf.pucp.edu.pe:1433;"+
                                    "databaseName=inf282;user=inf282;password=inf282db;";
            conn = DriverManager.getConnection(connectionUrl);
            String SQLString =  "DELETE FROM G04_DOCUMENTO WHERE IDDOCUMENTO = "+id+"";
            
            pstmt = conn.prepareStatement(SQLString);               
                
            result =  pstmt.executeUpdate();
            
            if (result == 0){
                throw new Exception();
            }            
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
             //5. Se cierra la conexión
             try {if (rs != null) rs.close(); } 
             catch(Exception e){e.printStackTrace();}  
        } 
        if(result==1){
        JOptionPane.showMessageDialog(null,"      Operación Exitosa!","Cliente",JOptionPane.INFORMATION_MESSAGE);}
        return result;                
    }

    
    
}
